/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author Gjun
 */
public class DAO {
    int rowcount=3;
    static{
        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        } catch (Exception ex) {
            System.out.println("error");
        }
    }
    
    Connection getConnection()throws Exception{
                //Connection con 
                // = java.sql.DriverManager.getConnection("jdbc:sqlserver://127.0.0.1:1433", "javadb", "java");
          Context x = new InitialContext();
          Context y = (Context)x.lookup("java:/comp/env");
          DataSource z = (DataSource)y.lookup("jdbc/mssql");
          Connection con = z.getConnection();
          return con;
       } 
    public int modify(String sql,String param[]) throws Exception{
        int x =0;
        Connection con =null;
        try{
            con=getConnection();
            PreparedStatement ps = null;
            try{
                ps=con.prepareStatement(sql);
                for (int i =0; i< param.length;i++)
                    ps.setString(i+1, param[i]);
                x = ps.executeUpdate();
            }finally{
            ps.close();
            }
        }finally{
            con.close();
        }
        return x;
    }
    
    public ArrayList<HashMap> query(String sql,String param[]) throws Exception{
        ArrayList<HashMap> rsList = new ArrayList<HashMap>();
        Connection con =null;
        try{
            con=getConnection();
            PreparedStatement ps = null;
            try{
                ps=con.prepareStatement(sql);
                for (int i =0; i< param.length;i++)
                    ps.setString(i+1, param[i]);
                java.sql.ResultSet rs = null;
                try{
                    rs = ps.executeQuery();
                    java.sql.ResultSetMetaData meta = rs.getMetaData();
                    int count = meta.getColumnCount();
                    while (rs.next()){
                        HashMap map =new HashMap();
                        for(int i=1;i<=count;i++){
                            String key =meta.getColumnName(i);
                            String value =rs.getString(i);
                            map.put(key, value);
                        }
                        rsList.add(map);
                    }                                        
                }finally{
                    rs.close();
                }
            }finally{
            ps.close();
            }
        }finally{
            con.close();
        }
        return rsList;
    }
    public ArrayList<HashMap> query2(String sql,String param[],String pag)throws Exception
    {
        ArrayList<HashMap> reList=new ArrayList<HashMap>();
        
        Connection con=null;
        try{
            con=getConnection();
            java.sql.PreparedStatement ps=null;
            try{
                String sql2=sql+"order by id offset "+(rowcount*(Integer.parseInt(pag)-1))+" rows fetch next "+
                        rowcount+" rows only";
              
                ps=con.prepareStatement(sql2);
               
            for(int i=0;i<param.length;i++)
            {
             ps.setString(i+1,param[i]);
            }
            java.sql.ResultSet rs=null;
            try{
                rs=ps.executeQuery();
              java.sql.ResultSetMetaData meta=rs.getMetaData();
              int count=meta.getColumnCount();
              while(rs.next())
              {
                  HashMap map=new HashMap();
                  for(int i=1;i<=count;i++)
                  {
                      String key=meta.getColumnName(i);
                      String value=rs.getString(i);
                    
                      map.put(key, value);
                  }
                  reList.add(map);
              }
                
            }finally{
                rs.close();
            }
                    
        }finally{
            ps.close();
        }
        }finally{
        con.close();
        }
        return reList;
    }
    
  
   public comm.comm getcomm(String id)throws Exception{
      
       Connection con=null;
            comm.comm c=null;
        try{
            con=getConnection();
            java.sql.PreparedStatement ps=null;
            try{
                String sql="SELECT 產品名稱,產品價格,產品介紹 from 產品維護 where id= "+id;
                System.out.println(sql);
                ps=con.prepareStatement(sql);
               
           
            java.sql.ResultSet rs=null;
            try{
                rs=ps.executeQuery();
            
              while(rs.next()){
             
             c=new comm.comm(rs.getString(1),rs.getString(2),rs.getString(3));
              }
            }finally{
                rs.close();
            }
                    
        }finally{
            ps.close();
        }
        }finally{
        con.close();
        }
                
                   
       return c;
   
   } 
 
   public String  getLast()throws Exception
   {
       String num=null;
     Connection con=null;
            comm.comm c=null;
        try{
            con=getConnection();
            java.sql.PreparedStatement ps=null;
            try{
                 String sql="select top 1 id from 產品維護 order by id desc";               
                ps=con.prepareStatement(sql);   
            java.sql.ResultSet rs=null;
            try{
                rs=ps.executeQuery();
              
              while(rs.next()){
              num=rs.getString(1);
              }
            }finally{
                rs.close();
            }
                    
        }finally{
            ps.close();
        }
        }finally{
        con.close();
        }                 
       return num;
   
   } 
   public int insert(String user,String address,ArrayList<comm.comm> list) throws Exception
   {
       
       int x=0;
       Connection con =null;
        try{
            con=getConnection();
            
            PreparedStatement ps = con.prepareStatement("INSERT INTO 訂單清單(訂購會員,地址,商品名稱,商品數量,商品價格,訂購日期) "
                    + "values(?,?,?,?,?,?)");
            
                    try{
                                 SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
                                 Date date = new Date();
                                 String strDate = sdFormat.format(date);


                                for(comm.comm c:list)
                                {
                                        ps.setString(1,user);                                         
                                        ps.setString(2,address);
                                        ps.setString(3,c.getName());
                                         ps.setInt(4,c.getAddnum());
                                         ps.setInt(5,Integer.parseInt(c.getPrice()));
                                         ps.setString(6,strDate);
                                         x+= ps.executeUpdate();
                                        System.out.println(x);
                                }      
                             } finally{
                                    ps.close();                
                                }        
                }finally{
                con.close();
                 }
        return x;
      
        }
   public  int checkcomm(comm.comm cc) throws Exception
   {
       int checkcomm=-1;       
       Connection con=null;
            comm.comm c=null;
        try{
            con=getConnection();
            java.sql.PreparedStatement ps=null;
            try{
                String sql="SELECT 庫存量 from 產品維護 where id= "+cc.getId();
                System.out.println(sql);
                ps=con.prepareStatement(sql);
               
           
            java.sql.ResultSet rs=null;
            try{
                rs=ps.executeQuery();
              
              while(rs.next()){
                if(rs.getInt(1)<cc.getAddnum()){
                       checkcomm=rs.getInt(1);
                }
              }
            }finally{
                rs.close();
            }
                    
        }finally{
            ps.close();
        }
        }finally{
        con.close();
        }
       return checkcomm;
   }   
   public  int updatecomm(ArrayList<comm.comm> list) throws Exception
   {
             int result=0;
       Connection con=null;
       Statement stmt=null;
        try{            
            con=getConnection();            
            try{
                for(comm.comm c:list)
                {
                    stmt=con.createStatement();
                String sql1="UPDATE 產品維護 SET 庫存量= 庫存量-"+c.getAddnum()+" WHERE id="+c.getId();                       
                System.out.println(sql1);
               result=stmt.executeUpdate(sql1);
                  System.out.println("1");
                if (result==0)break;
                System.out.println("2");
                }
            }finally{
            stmt.close();
        }
        }finally{
        con.close();
        }
       return result;
   }   
}




